﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;
using System.Data.SqlTypes;
/// <summary>
/// Dataaccess class for FlightInstance
/// </summary>
namespace DBLayer
{
    public class FlightInstanceDB
    {
        //Variabler initialization
        private SqlConnection con;
        private SqlUtility sqlUtility;
        private FlightDB flightDb;
        private PlaneDB planeDb;

        //Used for creating test data
        private Random random;
         
        public FlightInstanceDB()
        {
            sqlUtility = new SqlUtility();
            flightDb = new FlightDB();
            planeDb = new PlaneDB();
            random = new Random();
        }
        //Count rows in table in database
        private int CountDatabase()
        {
            int count;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "Select COUNT (FlightInstanceId) from FlightInstance";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    count = (int)cmd.ExecuteScalar();
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return count;
        }
        //Returns a string of af datetime so starting zeros are kept
        private string GetTimeString(DateTime _date)
        {
            string date = _date.Hour + ":" + _date.Minute;
            if (_date.Hour < 10)
            {
                date = "0" + _date.Hour + ":" + _date.Minute;
            }
            if (_date.Minute < 10)
            {
                date = _date.Hour + ":" + "0" + _date.Minute;
            }
            return date;
        }

        //Returns date string  so starting zeros are kept
        private string GetDateString(DateTime _date)
        {
            string date = _date.Day + "/" + _date.Month + "/" + _date.Year;
            if (_date.Day < 10)
            {
                date = "0" + _date.Day + "/" + _date.Month + "/" + _date.Year;
            }
            if (_date.Month < 10)
            {
                date = _date.Day + "/" + "0" + _date.Month + "/" + _date.Year;
            }
            return date;
        }

        //Insert FlightInstance into database 
        public int InsertFlightInstance(FlightInstance _flightInstance)
        {
            int count = CountDatabase() + 1;
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query =
                "INSERT INTO FlightInstance (flightInstanceId, arrivalTime, departureTime, routeId, planeNo) VALUES (@flightInstanceId, @arrivalTime, @departureTime, @routeId, @planeNo)";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@flightInstanceId", count);
            cmd.Parameters.AddWithValue("@arrivalTime", _flightInstance.ArrivalTime);
            cmd.Parameters.AddWithValue("@departureTime", _flightInstance.DepartureTime);
            cmd.Parameters.AddWithValue("@routeId", _flightInstance.Flight.RouteId);
            cmd.Parameters.AddWithValue("@planeNo", _flightInstance.Plane.PlaneNo);

            return sqlUtility.ExecuteData(cmd, connect);
        }
        //find flights instance by flight object 
        public List<FlightInstance> FindFlightInstanceByFlight(Flight _flight)
        {

            List<FlightInstance> retList = new List<FlightInstance>();

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "Select * from Flightinstance WHERE routeId = @routeId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            FlightInstance temFlightInstace = BuildFlightInstance(rdr);
                            retList.Add(temFlightInstace);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (FlightInstance fi in retList)
            {
                fi.Flight = flightDb.FindFlightByID(fi.Flight.RouteId);
                fi.Plane = planeDb.FindPlaneByPlaneNo(fi.Plane.PlaneNo);
            }
            return retList;
        }
        //Returns next flightinstance
        public List<FlightInstance> FindNextFlightInstanceByFlight(Flight _flight, DateTime _flightTime)
        {

            List<FlightInstance> retList = new List<FlightInstance>();

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select * from flightinstance where routeId = @routeId and departureTime > @departureTime";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
                    cmd.Parameters.AddWithValue("@departureTime", _flightTime);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            FlightInstance temFlightInstace = BuildFlightInstance(rdr);
                            retList.Add(temFlightInstace);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (FlightInstance fi in retList)
            {
                fi.Flight = flightDb.FindFlightByID(fi.Flight.RouteId);
                fi.Plane = planeDb.FindPlaneByPlaneNo(fi.Plane.PlaneNo);
            }
            return retList;
        }
        //find FlightInstance by int (id)
        public FlightInstance FindFlightInstanceById(int _id)
        {
            bool exists = false;
            FlightInstance retFlightInstance = new FlightInstance();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from FlightInstance WHERE flightInstanceId = @flightInstanceId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightInstanceId", _id);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            retFlightInstance = BuildFlightInstance(rdr);
                            exists = true;
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            if (exists)
            {
                retFlightInstance.Flight = flightDb.FindFlightByID(retFlightInstance.Flight.RouteId);
                retFlightInstance.Plane = planeDb.FindPlaneByPlaneNo(retFlightInstance.Plane.PlaneNo);
            }
            return retFlightInstance;
        }
        
        //returns a list<FlightInstance> with only FlightInstance on a specifik date and route
        public List<FlightInstance> FindFlightInstanceByFlightAndDate(Flight _flight, DateTime _date)
        {
            List<FlightInstance> retList = new List<FlightInstance>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "Select * from flight inner join flightinstance on FlightInstance.routeId=Flight.routeId " +
               "WHERE FlightInstance.routeId= @routeId AND flightDate= @flightDate";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightDate", GetDateString(_date));
                    cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            FlightInstance temFlightInstace = BuildFlightInstance(rdr);
                            retList.Add(temFlightInstace);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (FlightInstance fi in retList)
            {
                fi.Flight = flightDb.FindFlightByID(fi.Flight.RouteId);
                fi.Plane = planeDb.FindPlaneByPlaneNo(fi.Plane.PlaneNo);
            }
            return retList;
        }
        public bool HasValiedConnection(Airport _startAirport, Airport _endAirport, DateTime _flightTime)
        {

            bool connected = false;

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select * from flightinstance fi join flight f on fi.routeId = f.routeId where startAirportCode = @startAirportCode and endAirportCode = @endAirportCode and departureTime > @departureTime";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@startAirportCode", _startAirport.AirportCode);
                    cmd.Parameters.AddWithValue("@endAirportCode", _endAirport.AirportCode);
                    cmd.Parameters.AddWithValue("@departureTime", _flightTime);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {

                            connected = true;

                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return connected;
        }

        //Update FlightInstance in database by obejct 
        public int UpdateFlightInstance(FlightInstance _flightInstance)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "UPDATE FlightInstance SET flightInstanceId = @flightInstanceId, arrivalTime = @arrivalTime, departureTime = @departureTime, routeId = @routeId, planeNo = @planeNo " +
                                 "WHERE flightInstanceId = @flightInstanceId";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@arrivalTime", _flightInstance.ArrivalTime);
            cmd.Parameters.AddWithValue("@departureTime", _flightInstance.DepartureTime);
            cmd.Parameters.AddWithValue("@routeId", _flightInstance.Flight.RouteId);
            cmd.Parameters.AddWithValue("@planeNo", _flightInstance.Plane.PlaneNo);
            cmd.Parameters.AddWithValue("@flightInstanceId", _flightInstance.FlightInstanceId);
            return sqlUtility.ExecuteData(cmd, connect);
        }
       
        //Find the next valid flightinstance in a 14 days range
        public FlightInstance ValidNextLink(Flight _flight, DateTime flightTime)
        {
            List<FlightInstance> instances = new List<FlightInstance>();
            int days = 14;
            DateTime limit = flightTime.AddDays(days);
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select * from flightinstance where routeId = @routeId and departureTime between @departureTime and @limit";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
                    cmd.Parameters.AddWithValue("@departureTime", flightTime);
                    cmd.Parameters.AddWithValue("@limit", limit);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            FlightInstance ins = BuildFlightInstance(rdr);
                            instances.Add(ins);
                        }

                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            List<FlightInstance> sortedList = instances.OrderBy(fi => fi.DepartureTime).ToList();
            return sortedList[0];
        }
       
        //build object FlightInstance from data from database
        private FlightInstance BuildFlightInstance(SqlDataReader _reader)
        {
            FlightInstance temFlightInstance = new FlightInstance();
            temFlightInstance.FlightInstanceId = (int)_reader["flightInstanceId"];
            //Param shows which occurence of datetime must go where
            temFlightInstance.ArrivalTime = _reader.GetDateTime(1);
            temFlightInstance.DepartureTime = _reader.GetDateTime(2);
            temFlightInstance.FlightTime = temFlightInstance.ArrivalTime - temFlightInstance.DepartureTime;

            int id = (int)_reader["routeId"];
            temFlightInstance.Flight = flightDb.FindFlightByID(id);

           
            string plane = (string)_reader["planeNo"];
            temFlightInstance.Plane = planeDb.FindPlaneByPlaneNo(plane);


            return temFlightInstance;
        }
        //return a List<FlightInstance> with all the FlightInstance in the database
        public List<FlightInstance> SelectAll()
        {
            List<FlightInstance> InstanceList = new List<FlightInstance>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from FlightInstance";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            FlightInstance temFlightInstance = BuildFlightInstance(rdr);
                            InstanceList.Add(temFlightInstance);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (FlightInstance fi in InstanceList)
            {
                fi.Flight = flightDb.FindFlightByID(fi.Flight.RouteId);
                fi.Plane = planeDb.FindPlaneByPlaneNo(fi.Plane.PlaneNo);
            }
            return InstanceList;
        }
        //remove FlightInstance from database
        public int RemoveFlightInstance(FlightInstance _flightInstance)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from FlightInstance WHERE flightInstanceId = @flightInstanceId";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@flightInstanceId", _flightInstance.FlightInstanceId);
            return sqlUtility.ExecuteData(cmd, connect);
        }
        //Method for getting test data
        public void TestDataInsert()
        {
            List<Flight> allFlights = flightDb.SelectAll();

            
            foreach (Flight f in allFlights)
            {
                int count = CountDatabase() + 1;
                DateTime start = GenerateDate();
                DateTime end = start.AddHours(random.Next(10)+2);

                SqlConnection connect = sqlUtility.GetCon();

                connect.Open();
                const string query =
                    "INSERT INTO FlightInstance (flightInstanceId, arrivalTime, departureTime, routeId, planeNo) VALUES (@flightInstanceId, @arrivalTime, @departureTime, @routeId, @planeNo)";
                SqlCommand cmd = new SqlCommand(query, connect);
                cmd.Parameters.AddWithValue("@flightInstanceId", count);
                cmd.Parameters.AddWithValue("@arrivalTime", end);
                cmd.Parameters.AddWithValue("@departureTime", start);
                cmd.Parameters.AddWithValue("@routeId", f.RouteId);
                cmd.Parameters.AddWithValue("@planeNo", random.Next(8)+1);
               
                sqlUtility.ExecuteData(cmd, connect);
                
            }
             
        }
        //Generates datetime for test data
        public DateTime GenerateDate()
        {
           
                DateTime startDate = new DateTime(2013, 1, 1);
                DateTime endDate = new DateTime(2014, 1, 1);
                TimeSpan timeSpan = endDate - startDate;

                TimeSpan newSpan = new TimeSpan(0, random.Next(0, (int)timeSpan.TotalMinutes), 0);

                DateTime newRandom = startDate + newSpan;

                return newRandom;
        }
    }
}


